import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Class ClickerDB
 * Handles Database Connection
 * 
 * @author kai
 *
 */
public class ClickerDB {
	private String host;
	private String user;
	private String password;
	private String database;
	private Connection dbConnection = null;
	
	/**
	 * Constructor
	 * 
	 * @param host server
	 * @param user username
	 * @param password password of username
	 * @param database database to use
	 */
	public ClickerDB(String host, String user, String password, String database) {
		/* Initiate Attributes */
		this.host 		= host;
		this.user 		= user;
		this.password 	= password;
		this.database 	= database;
		
		/* On Initialization Connect to DB */
		this.initDBConnection();
	}

	/**
	 * Method initDBConnection()
	 * Initiate the Connection to the Database
	 */
	private void initDBConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			this.dbConnection = DriverManager.getConnection("jdbc:mysql://" + this.host + "/"+ this.database +"", this.user, this.password);
			if(!this.dbConnection.isClosed()) {
				System.out.println("connected");	    	 
			}
		} catch (InstantiationException e) {
			// error
		} catch (IllegalAccessException e) {
			// error
		} catch (ClassNotFoundException e) {
			// error
		} catch (SQLException e) {
			// error
		}	
	}

	/**
	 * Method doQuery()
	 * execute the queryStr
	 * 
	 * @param queryStr Query String in SQL-format (e.g. SELECT * FROM table)
	 * @return ResultSet
	 */
	private ResultSet doQuery(String queryStr) {
	      Statement query;
	      ResultSet RS = null;

	      try {
	    	  query = this.dbConnection.createStatement();
	    	  query.execute(queryStr);
		      RS = query.getResultSet();

		} catch (SQLException e) {
			// error
		}
		return RS;
	}
	
	/**
	 * Method getAnswersByQuestionId()
	 * returns a List of objects from class Question which 
	 * has the Id questionId.
	 * 
	 * @param questionId Id of the question saved in the database
	 * @return List of objects from class Question
	 */
	public ResultSet getAnswersByQuestionId(String questionId) {
		return this.doQuery("" +
				"SELECT" +
				"	vote.id AS vid," +
				"	vote.question AS vquestion," +
				"	answer.id AS aid," +
				"	answer_input.channel AS achannel," +
				"	answer." +
				"FROM" +
				"	vote LEFT JOIN " +
				"	(answer LEFT JOIN answer_input" +
				"	ON answer.id = answer_input.answer.id)" +
				"	ON vote.id = answer.vote_id");
	}
	
	/**
	 * Method saveAnswersFromList()
	 * save list of objects from class voteItem in the database.
	 * 
	 * @param list of objects from class voteItem
	 */
	public void saveAnswersFromList(List<voteItem> list) {
		for (voteItem item:list) {
			/* Check if Item exists in DB */
			boolean itemExists = true;
			ResultSet tmp;
			tmp = this.doQuery("SELECT " +
					"COUNT(id) AS idcount " +
					"FROM " +
					"answer_input " +
					"WHERE " +
					"question_vid = '" + 	item.getQuestionId() + "' && " +
					"timestamp = '" + 		item.getTimestamp() + "' && " +
					"sender = '" + 			item.getSender() + "' && " +
					"answer = '" + 			item.getAnswer() + "'");
			try {
				tmp.first();
				if (tmp.getInt("idcount") == 0) {
					itemExists = false;
				}
			} catch (SQLException e) {
				// error
			}
			if (!itemExists) {
				this.doQuery("INSERT " +
					"INTO " +
					"answer_input(" +
					"question_vid, " +
					"timestamp, " +
					"sender, " +
					"answer)" +
					" VALUES(" +
					"'" + item.getQuestionId() + "', " +
					"'" + item.getTimestamp() + "', " +
					"'" + item.getSender() + "', " +
					"'" + item.getAnswer() + "')");
			}
		}
	}
	
	/**
	 * Method getAllUser()
	 * Returns list of all users
	 * 
	 * @return List of all users as list of objects from class User
	 */
	public List<User> getAllUser() {
		ResultSet result = this.doQuery("" +
				"SELECT" +
				"	user.name AS uname," +
				"	user.vorname AS uvorname," +
				"	user.password AS upassword," +
				"	user.shortname AS ushortname" +
				"FROM" +
				"	user ");
		List<User> returnList = new ArrayList<User>();
		try {
			while(result.next()) {
				User tmpUser = new User();
				tmpUser.setName(result.getString("name"));
				returnList.add(tmpUser);
			}
		} catch (SQLException e) {
			//error
		}
		return returnList;
	}
	
	/**
	 * Method getUserData()
	 * returns Object from class User which contains the Data of the user
	 * with Id userId
	 * 
	 * @param userId Id which is saved in the Database
	 * @return returns object from class User
	 */
	public User getUserData(int userId) {
		User tmpUser = new User();
		try {
			ResultSet result;
			result =  this.doQuery("" +
					"SELECT" +
					"	user.name AS uname," +
					"	user.firstname AS ufirstname," +
					"	user.password AS upassword," +
					"	user.nickname AS unickname" +
					"FROM" +
					"	user " +
					"WHERE " +
					"	user.id = '" + userId + "'");
			result.first();
			tmpUser.setName(result.getString("name"));
			tmpUser.setFirstname(result.getString("firstname"));
			tmpUser.setNickname(result.getString("nickname"));
			tmpUser.setPassword(result.getString("password"));
			
		} catch (SQLException e) {
			// error
		}
		return tmpUser;
	}
	
	/**
	 * Method getQuestionByUserId()
	 * returns all questions as list of objects from class Question
	 * 
	 * @param userId Id of user
	 * @return list of objects from class Question
	 */
	public List<Question> getQuestionByUserId(int userId) {
		ResultSet result = this.doQuery("" +
				"SELECT" +
				"	* " +
				"FROM" +
				"	question " +
				"WHERE " +
				"	question.user_id = '" + userId + "'");
		
		List<Question> returnList = new ArrayList<Question>(); 
		try {
			while(result.next()) {
				Question tmpQuestion = new Question();
				tmpQuestion.setQuestion(result.getString("name"));
				returnList.add(tmpQuestion);
			}
		} catch (SQLException e) {
			//error
		}
		return returnList;
	}
	
	/**
	 * Method getCountUser()
	 * returns a count of users
	 * 
	 * @param nickname The users nickname (e.g. jschaefer)
	 * @param password The relating password to the User
	 * @return Count of Users with the criterial nickname and password as int
	 */
	public int getCountUser(String nickname, String password){
		int userCount = 0;
		try {
			ResultSet result = this.doQuery("" +
					"SELECT" +
					"	COUNT(id) AS countid " +
					"FROM" +
					"	user " +
					"WHERE " +
					"	shortname = '" + nickname + "' && " +
					"	password = '" + password + "'");
			result.first();
			userCount = result.getInt("countid");
		} catch (SQLException e) {
			// error
		}
		return userCount;
	}
}
